﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_AddTrainingRegister

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_SelectGroupInfoByGroupId(ByVal GroupId As Integer) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        '某一場次下的課程資訊
        sql.AppendFormat("SELECT A.AllHour,A.PersonLimit,A.Candidate,A.StartDate,A.EndDate,A.RegStartDate,A.RegEndDate,A.Org,A.ClassPlace ")
        sql.AppendFormat(", trAll.AllCount ")
        sql.AppendFormat(", isnull(trB.BCount,0) BCount ")
        sql.AppendFormat(", isnull(trN.NCount,0) NCount ")
        sql.AppendFormat(", isnull(trY.YCount,0) YCount ")
        sql.AppendFormat("FROM TrainingCourseGroup A  ")
        sql.AppendFormat("left join (select GroupID, Count(*) as AllCount from TrainingRegister where GroupID = '{0}' group by GroupID ) trAll on A.ID = trAll.GroupID ", GroupId)
        sql.AppendFormat("left join (select GroupID, Count(*) as BCount from TrainingRegister where GroupID = '{0}' and RegStatus='B' group by GroupID ) trB on A.ID = trB.GroupID ", GroupId)
        sql.AppendFormat("left join (select GroupID, Count(*) as NCount from TrainingRegister where GroupID = '{0}' and RegStatus='N' group by GroupID ) trN on A.ID = trN.GroupID ", GroupId)
        sql.AppendFormat("left join (select GroupID, Count(*) as YCount from TrainingRegister where GroupID = '{0}' and RegStatus='Y' group by GroupID ) trY on A.ID = trY.GroupID ", GroupId)
        sql.AppendFormat("WHERE A.Id={0}", GroupId)
        Return dat.GetData(sql)

    End Function

    Public Function BLL_SelectCourseInfoByGroupId(ByVal GroupId As Integer) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        '某一場次下的課程資訊
        sql.AppendFormat("SELECT A.AllHour,A.PersonLimit,A.Candidate,A.StartDate,A.EndDate,A.RegStartDate,A.RegEndDate,A.ClassPlace,A.Org,C.CourseName,B.TeacherName,C.Hour ")
        sql.AppendFormat(",B.StartTime, B.EndTime ")
        sql.AppendFormat("FROM TrainingCourseGroup A  ")
        sql.AppendFormat("inner join TrainingCourseGroupMapping B on A.Id= B.GroupId ")
        sql.AppendFormat("inner join TrainingCourse C on B.CourseId = C.Id ")
        sql.AppendFormat("WHERE A.Id={0}", GroupId)
        Return dat.GetData(sql)

    End Function

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal UOrganizationID As String, ByVal GroupId As Integer, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT  A.Id,A.VolunteerID,B.VolunteerName,B.PersonName,A.RegStatus,A.CheckStatus ")
        sql.AppendFormat("FROM TrainingRegister A ")
        sql.AppendFormat("LEFT Join VolunteerDistinctView B on A.VolunteerID = B.VolunteerID ")

        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")
        sql.AppendFormat(" LEFT JOIN GroupMappingView gmv on A.VolunteerID=gmv.VolunteerID ")
        sql.AppendFormat("WHERE B.Active in ('2','Y') AND A.GroupId={0} ", GroupId)
        If VOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.VOrganizationID = {0}  ) ", VOrganizationID)
        End If
        If UOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.DirectUOrganizationID = '{0}'  ) ", UOrganizationID)
        End If
        ' sql.AppendFormat("AND (B.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0} ) or '{0}' =0 ) ", VOrganizationID)
        'sql.AppendFormat("AND (B.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (B.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        If VolunteerId <> "" Then
            sql.AppendFormat("AND (B.VolunteerId = '{0}') ", VolunteerId)
        End If
        If VolunteerName <> "" Then
            sql.AppendFormat("AND (B.VolunteerName like '%{0}%') ", VolunteerName)
        End If

        If TEL <> "" Then
            sql.AppendFormat("AND (((B.MobilePhone like '%{0}%') or (B.OfficePhone like '%{0}%'))) ", TEL)

        End If
        sql.AppendFormat("Order by oo.VOrganizationName,gmv.GroupName,B.VolunteerName,A.Id,A.RegStatus,A.CheckStatus,B.PersonName ")

        ' MyObj.WriteLog(sql.ToString())
        Return dat.GetData(sql)

    End Function

    Public Function BLL_SelectActiveVolunteer(ByVal UOrganizationID As String, ByVal GroupId As Integer, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String) As DataTable
        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("SELECT  A.Id,A.VolunteerID,B.VolunteerName,B.PersonName,A.RegStatus,A.CheckStatus ")
        sql.AppendFormat("FROM TrainingRegister A ")
        sql.AppendFormat("LEFT Join VolunteerDistinctView B on A.VolunteerID = B.VolunteerID ")
        ' sql.AppendFormat("LEFT Join Village C on B.VillageID = C.RegionID ")

        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")
        sql.AppendFormat(" LEFT JOIN GroupMappingView gmv on A.VolunteerID=gmv.VolunteerID ")
        sql.AppendFormat("WHERE B.Active in ('2','Y') AND A.GroupId={0} ", GroupId)
        If VOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.VOrganizationID = {0}) ", VOrganizationID)
        End If

        If UOrganizationID <> "0" Then
            sql.AppendFormat("AND (oo.DirectUOrganizationID = '{0}'  ) ", UOrganizationID)
        End If
        ' sql.AppendFormat("AND (B.VolunteerID IN (SELECT distinct VolunteerID FROM VOrganizationMapping WHERE VOrganizationID={0} ) or '{0}' =0 ) ", VOrganizationID)
        'sql.AppendFormat("AND (B.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (B.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        If VolunteerId <> "" Then
            sql.AppendFormat("AND (B.VolunteerId = '{0}') ", VolunteerId)
        End If

        If VolunteerName <> "" Then
            sql.AppendFormat("AND (B.VolunteerName like '%{0}%' ) ", VolunteerName)
        End If
        If TEL <> "" Then
            sql.AppendFormat("AND (((B.MobilePhone like '%{0}%') or (B.OfficePhone like '%{0}%'))) ", TEL)
        End If
        sql.AppendFormat("AND A.RegStatus in ('Y','B') ")
        sql.AppendFormat("Order by oo.VOrganizationName,gmv.GroupName,B.VolunteerName,A.Id,A.RegStatus,A.CheckStatus,B.PersonName ")

        Return dat.GetData(sql)
    End Function

    'Update 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update)> _
    Public Function BLL_Update(ByVal original_Id As Integer, ByVal RegStatus As String) As Integer

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.AppendFormat("UPDATE TrainingRegister SET RegStatus='{0}' WHERE Id={1} ", RegStatus, original_Id)

            dat.AccData(sql)
            scope.Complete()

            Return 1

        End Using

    End Function

    'Delete 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Sub BLL_Delete(ByVal original_Id As Integer)

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Using scope As New Transactions.TransactionScope

            sql.Length = 0
            sql.AppendFormat("DELETE TrainingRegister WHERE Id={0}; ", original_Id)

            dat.AccData(sql)
            scope.Complete()

        End Using

    End Sub

End Class
